package ch.upndown.upndown.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import ch.upndown.upndown.utils.Defaults;
import ch.upndown.upndown.model.Website;

/**
 * Created by Bernard on 02.08.2014.
 * Changed by Michel on 03.08.2014
 */
public class DatabaseAccess {

    private static DatabaseAccess instance;

    private final DatabaseCreator myCreator;
    private final SQLiteDatabase myDb;

    private DatabaseAccess(Context c) {
        myCreator = new DatabaseCreator(c);
        myDb = myCreator.getReadableDatabase();
    }

    public static DatabaseAccess getInstance(Context c) {
        if (instance == null)
            instance = new DatabaseAccess(c);
        return instance;
    }

    public long addWebsite(Website website) {
        ContentValues values = new ContentValues();
        values.put(DatabaseCreator.Websites.COLUMN_NAME_TITLE, website.getTitle());
        values.put(DatabaseCreator.Websites.COLUMN_NAME_URL, website.getUrl());
        return myDb.insert(DatabaseCreator.Websites.TABLE_NAME, "null", values);
    }

    public void addCheckOrder(Website website, int checkId) {
        ContentValues values = new ContentValues();
        values.put(DatabaseCreator.WebsitesChecks.COLUMN_NAME_ENTRY_WEBSITE_ID, website.getId());
        values.put(DatabaseCreator.WebsitesChecks.COLUMN_NAME_ENTRY_CHECKS_ID, checkId);
        myDb.insert(DatabaseCreator.WebsitesChecks.TABLE_NAME, "null", values);
    }

    public void addCheckToHistory(Website website, int checkId, int exitCode) {
        SimpleDateFormat dateFormat = new SimpleDateFormat(Defaults.dateFormat);
        Date date = new Date();
        ContentValues values = new ContentValues();
        values.put(DatabaseCreator.CheckHistory.COLUMN_NAME_ENTRY_WEBSITE_ID, website.getId());
        values.put(DatabaseCreator.CheckHistory.COLUMN_NAME_ENTRY_CHECKS_ID, checkId);
        values.put(DatabaseCreator.CheckHistory.COLUMN_NAME_EXIT_CODE, exitCode);
        values.put(DatabaseCreator.CheckHistory.COLUMN_NAME_TIMESTAMP, dateFormat.format(date));
        myDb.insert(DatabaseCreator.CheckHistory.TABLE_NAME, "null", values);
    }

    public List<Website> getWebsites() {
        return queryWebsite(null, null);
    }

    public Website getWebsiteById(int id) {
        String selection = DatabaseCreator.Websites.COLUMN_NAME_ENTRY_ID + " LIKE ?";
        String[] selectionArgs = {String.valueOf(id)};
        List<Website> matchingWebsites = queryWebsite(selection, selectionArgs);
        if (matchingWebsites.size() == 0)
            return null;
        return matchingWebsites.get(1);
    }

    private List<Website> queryWebsite(String selection, String[] selectionArgs) {
        String[] projection = {DatabaseCreator.Websites.COLUMN_NAME_ENTRY_ID,
                DatabaseCreator.Websites.COLUMN_NAME_TITLE,
                DatabaseCreator.Websites.COLUMN_NAME_URL};
        Cursor c = myDb.query(DatabaseCreator.Websites.TABLE_NAME,
                projection, null, null, null, null, null);
        List<Website> result = new ArrayList<Website>();
        while (c.moveToNext()) {
            Website website = new Website();
            website.setId(c.getInt(c.getColumnIndex(DatabaseCreator.Websites.COLUMN_NAME_ENTRY_ID)));
            website.setTitle(c.getString(c.getColumnIndex(DatabaseCreator.Websites.COLUMN_NAME_TITLE)));
            website.setUrl(c.getString(c.getColumnIndex(DatabaseCreator.Websites.COLUMN_NAME_URL)));
            result.add(website);
        }
        return result;
    }

    public void deleteWebsite(Website website) {
        String selection = DatabaseCreator.Websites.COLUMN_NAME_ENTRY_ID + " LIKE ?";
        String[] selectionArgs = {String.valueOf(website.getId())};
        myDb.delete(DatabaseCreator.Websites.TABLE_NAME, selection, selectionArgs);
    }

    public void deleteCheckOrder(Website website, int checkId) {
        String selection = DatabaseCreator.WebsitesChecks.COLUMN_NAME_ENTRY_WEBSITE_ID + " LIKE ? " +
                "AND " + DatabaseCreator.WebsitesChecks.COLUMN_NAME_ENTRY_CHECKS_ID + " LIKE ?";
        String[] selectionArgs = {String.valueOf(website.getId()), String.valueOf(checkId)};
        myDb.delete(DatabaseCreator.WebsitesChecks.TABLE_NAME, selection, selectionArgs);
    }

    /** Diese Funktion ist dafür gedacht, die Einträge gewisser Webseiten zu löschen. Evtl. brauchen wir sie etwas abgeändert... */
    public void deleteCheckHistory(Website website) {
        String selection = DatabaseCreator.CheckHistory.COLUMN_NAME_ENTRY_WEBSITE_ID + " LIKE ?";
        String[] selectionArgs = {String.valueOf(website.getId())};
        myDb.delete(DatabaseCreator.CheckHistory.TABLE_NAME, selection, selectionArgs);
    }

}
